﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Yu.Cms.Common;
using Yu.Cms.IDAL;

namespace Yu.Cms.SQLServerDAL
{
    public class album : Ialbum
    {
        private readonly string TableName = "Y_album";
        #region Ialbum 成员
        /// <summary>
        /// 该相册是否存在照片
        /// </summary>
        /// <param name="alBigID"></param>
        /// <returns></returns>
        public bool isExistPhoto(int alBigID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from  " + TableName);
            strSql.Append(" where alBigID=@alBigID ");
            SqlParameter[] parameters = {
					new SqlParameter("@alBigID", SqlDbType.Int,4)};
            parameters[0].Value = alBigID;
            return DbHelperMsSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到最新插入的数据
        /// </summary>
        /// <returns></returns>
        public Yu.Cms.Model.album GetTopModel()
        {
            QueryParam qp = new QueryParam();
            qp.Where = "";
            Yu.Cms.Model.album fam = new Yu.Cms.Model.album();
            int RecordCount;
            IList<Yu.Cms.Model.album> lst = this.GetList(qp, out RecordCount);
            if (lst.Count > 0)
            {
                fam = lst[0];
            }
            return fam;
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Yu.Cms.Model.album model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into " + TableName + "(");
            strSql.Append("alBigID,alTitle,alInfo,alPath,alThumbPath,pubTime)");
            strSql.Append(" values (");
            strSql.Append("@alBigID,@alTitle,@alInfo,@alPath,@alThumbPath,@pubTime)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@alBigID", SqlDbType.Int,4),
					new SqlParameter("@alTitle", SqlDbType.NVarChar,14),
					new SqlParameter("@alInfo", SqlDbType.NVarChar,150),
                    new SqlParameter("@alPath",SqlDbType.NVarChar,150),					
                    new SqlParameter("@alThumbPath",SqlDbType.NVarChar,150),
					new SqlParameter("@pubTime", SqlDbType.SmallDateTime)
				
				 };
            parameters[0].Value = model.alBigID;
            parameters[1].Value = model.alTitle;
            parameters[2].Value = model.alInfo;
            parameters[3].Value = model.alPath;
            parameters[4].Value = model.alThumbPath; 
            parameters[5].Value = model.pubTime;
           
            object obj = DbHelperMsSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                DbHelperMsSQL.Update_Table_Fileds("Y_albumType", "alphotoCount=alphotoCount+1", "alBigID=" + model.alBigID); //更新相册+1
                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(Yu.Cms.Model.album model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + TableName + " set ");
            strSql.Append("alBigID=@alBigID,");
            strSql.Append("alTitle=@alTitle,");
            strSql.Append("alInfo=@alInfo,");
            strSql.Append("alPath=@alPath,");
            strSql.Append("alkey=@alkey,");
            strSql.Append("pubTime=@pubTime ");            
            strSql.Append(" where alId=@alId ");
            SqlParameter[] parameters = {
					new SqlParameter("@alId", SqlDbType.Int,4),
					new SqlParameter("@alBigID", SqlDbType.Int,4),
					new SqlParameter("@alTitle", SqlDbType.NVarChar,14),
					new SqlParameter("@alInfo", SqlDbType.NVarChar,150),
                    new SqlParameter("@alPath",SqlDbType.NVarChar,150),
					new SqlParameter("@alkey", SqlDbType.NVarChar,30),
					new SqlParameter("@pubTime", SqlDbType.SmallDateTime)
					
					};
            parameters[0].Value = model.alId;
            parameters[1].Value = model.alBigID;
            parameters[2].Value = model.alTitle;
            parameters[3].Value = model.alInfo;
            parameters[4].Value = model.alPath;
            parameters[5].Value = model.alkey;
            parameters[6].Value = model.pubTime;
           
           return DbHelperMsSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="alId"></param>
        /// <returns></returns>
        public int Delete(int alId)
        {
            int alBigID = GetModel(alId).alBigID; //先获取alBigID
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + TableName);
            strSql.Append(" where alId=@alId ");
            SqlParameter[] parameters = {
					new SqlParameter("@alId", SqlDbType.Int,4)};
            parameters[0].Value = alId;
            int obj = DbHelperMsSQL.ExecuteSql(strSql.ToString(), parameters);
            if (obj != 0)
            {               
                DbHelperMsSQL.Update_Table_Fileds("Y_albumType", "alphotoCount=alphotoCount-1", "alBigID=" + alBigID); //相片数量-1
            }
            return Convert.ToInt32(obj);

        }

        /// <summary>
        /// 删除照片By BigID
        /// </summary>
        /// <param name="alBigID"></param>
        /// <returns></returns>
        public int DelByeBigID(int alBigID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + TableName);
            strSql.Append(" where alBigID=@alBigID ");
            SqlParameter[] parameters = {
					new SqlParameter("@alBigID", SqlDbType.Int,4)};
            parameters[0].Value = alBigID;
            return DbHelperMsSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 该ID有多少照片
        /// </summary>
        /// <param name="alBigID"></param>
        /// <returns></returns>
        public int GetCountByBigID(int alBigID)
        {
            QueryParam qp = new QueryParam();
            qp.Where = " Where alBigID=" + alBigID;
            Yu.Cms.Model.album fam = new Yu.Cms.Model.album();
            int RecordCount;
            IList<Yu.Cms.Model.album> lst = this.GetList(qp, out RecordCount);
            return RecordCount;            
        }

        /// <summary>
        /// 根据alId返回 album 实体类 单笔资料 (Y_album)
        /// </summary>
        /// <param name="alId">alId</param>
        /// <returns>返回 album实体类 alId为0则无记录</returns>
        public Yu.Cms.Model.album GetModel(int alId)
        {
            QueryParam qp = new QueryParam();
            qp.Where = " Where alId=" + alId;
            Yu.Cms.Model.album fam = new Yu.Cms.Model.album();
            int RecordCount;
            IList<Yu.Cms.Model.album> lst = this.GetList(qp, out RecordCount);
            if (lst.Count > 0)
            {
                fam = lst[0];
            }
            return fam;
        }

        /// <summary>
        /// 返回album实体类的IList对象 (Y_albumType)
        /// </summary>
        /// <param name="CurrentPageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="RecordCount"></param>
        /// <returns></returns>
        public IList<Yu.Cms.Model.album> GetList(int alBigID, int CurrentPageIndex, int PageSize, out int RecordCount)
        {
            QueryParam qp = new QueryParam();
            qp.Where = " Where alBigID=" + alBigID;
            qp.PageIndex = CurrentPageIndex;
            qp.PageSize = PageSize;
            return this.GetList(qp, out RecordCount);
        }

        /// <summary>
        /// 获取前iTop张照片
        /// </summary>
        /// <param name="iTop"></param>
        /// <returns></returns>
        public IList<Yu.Cms.Model.album> GetNewList(int iTop)
        {
            QueryParam qp = new QueryParam();
            qp.PageIndex = 1;
            qp.PageSize = iTop;            
            int RecordCount;
            return this.GetList(qp, out RecordCount);
        }
        
        #endregion

        /// <summary>
        /// 将记录集转为album实体类 (Y_album)
        /// </summary>
        /// <param name="dr">记录集</param>
        /// <param name="Fileds">字段名列表</param>
        /// <returns>album</returns>
        protected Yu.Cms.Model.album Populate_album(IDataReader dr, Dictionary<string, string> Fileds)
        {
            Yu.Cms.Model.album nc = new Yu.Cms.Model.album();

            if (Fileds.ContainsKey("alId") && !Convert.IsDBNull(dr["alId"])) nc.alId = Convert.ToInt32(dr["alId"]); // 自动ID
            if (Fileds.ContainsKey("alBigID") && !Convert.IsDBNull(dr["alBigID"])) nc.alBigID = Convert.ToInt32(dr["alBigID"]); // 相册分类ID
            if (Fileds.ContainsKey("alTitle") && !Convert.IsDBNull(dr["alTitle"])) nc.alTitle = Convert.ToString(dr["alTitle"]).Trim(); // 相片名称
            if (Fileds.ContainsKey("alInfo") && !Convert.IsDBNull(dr["alInfo"])) nc.alInfo = Convert.ToString(dr["alInfo"]).Trim(); // 相片介绍
            if (Fileds.ContainsKey("alPath") && !Convert.IsDBNull(dr["alPath"])) nc.alPath = Convert.ToString(dr["alPath"]).Trim(); // 相片路径
            if (Fileds.ContainsKey("alThumbPath") && !Convert.IsDBNull(dr["alThumbPath"])) nc.alThumbPath = Convert.ToString(dr["alThumbPath"]).Trim(); // 相片路径            
            if (Fileds.ContainsKey("alkey") && !Convert.IsDBNull(dr["alkey"])) nc.alkey = Convert.ToString(dr["alkey"]).Trim(); // 标签
            if (Fileds.ContainsKey("pubTime") && !Convert.IsDBNull(dr["pubTime"])) nc.pubTime = Convert.ToDateTime(dr["pubTime"]); // 发布日期           
            return nc;
        }

        /// <summary>
        /// 返回album实体类的IList对象 (Y_album)
        /// </summary>
        /// <param name="qp">查询类(非安全函数,传入参数请进行Sql字符串过滤)</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>album实体类的IList对象(Y_album)</returns>
        public IList<Yu.Cms.Model.album> GetList(QueryParam qp, out int RecordCount)
        {
            qp.TableName = TableName;
            if (qp.Orderfld == null)
            {
                qp.Orderfld = "alId";
            }
            else if (qp.Orderfld != "alId")
            {
                qp.Orderfld += ",alId";
            }

            if (qp.ReturnFields == null)
            {
                qp.ReturnFields = "*";
            }
            else
            {
                qp.ReturnFields += ",";
                qp.ReturnFields += qp.Orderfld;
            }
            DbHelperMsSQL.PopulateDelegate<Yu.Cms.Model.album> mypd = new DbHelperMsSQL.PopulateDelegate<Yu.Cms.Model.album>(this.Populate_album);
            return DbHelperMsSQL.GetRecordListBySql(mypd, qp, out RecordCount);
        }
    }
}
